import cn.hutool.core.util.StrUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.Objects;

/**
 * All rights Reserved, Designed By www.maihaoche.com
 *
 * @Package com.golf.core.util
 * @author: 皮皮（pipi@maihaoche.com）
 * @date: 2019/7/16 9:17 AM
 * @Copyright: 2017-2020 www.maihaoche.com Inc. All rights reserved.
 * 注意：本内容仅限于卖好车内部传阅，禁止外泄以及用于其他的商业目
 */
public class ExcelUtil {

    private static NumberFormat numberFormat = NumberFormat.getNumberInstance();

    static {
        numberFormat.setGroupingUsed(false);
    }

    /**
     * 判断excel某行是否为空
     */
    public static boolean isRowEmpty(Row row) {
        if (Objects.isNull(row)) {
            return true;
        }

        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                return false;
            }
        }
        return true;

    }

    /**
     * 判断excel某单元格是否为空
     */
    public static boolean isEmptyCell(Cell cell) {
        return Objects.isNull(cell) || HSSFCell.CELL_TYPE_BLANK == cell.getCellType();
    }

    /**
     * 获取excel某单元格内容
     * 若为空返回""
     */
    public static String getCellString(Cell cell) {
        String cellString;
        if (cell == null) {
            cellString = "";
        } else {
            try {
                cellString = cell.getStringCellValue();
            } catch (Exception e) {
                cellString = "";
            }
        }

        return StringUtils.trimToEmpty(cellString);
    }

    /**
     * excel某单元格内容转为stirng
     */
    public static String cellConvertToString(Cell cell){

        String data = "";
        if (null != cell) {
            int type = cell.getCellType();
            //判断为文本类型还是数字类型
            if (type == Cell.CELL_TYPE_NUMERIC) {
                double account = cell.getNumericCellValue();
                //格式化设置
                DecimalFormat decimalFormat = new DecimalFormat("#");
                //将double转字符串
                data = decimalFormat.format(account);
            } else if (type == Cell.CELL_TYPE_STRING) {
                data = cell.getStringCellValue();
                //去调字符串中的所有空格
                data = data.replace(" ", "");
            } else if (type == Cell.CELL_TYPE_FORMULA) {
                try {
                    data = numberFormat.format(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    data = numberFormat.format(cell.getRichStringCellValue());
                }
            }
        }
        return data;

    }

    /**
     * excel某单元格内容转为number
     */
    public static String cellConvertToNumber(Cell cell){

        String data = "";
        if (null != cell) {
            int type = cell.getCellType();
            //判断为文本类型还是数字类型
            if (type == Cell.CELL_TYPE_NUMERIC) {
                data = numberFormat.format(cell.getNumericCellValue());
            } else if (type == Cell.CELL_TYPE_STRING) {
                data = cell.getStringCellValue();
                //如果不是数字字符串则置空
                if (!NumberUtils.isCreatable(data)) {
                    data = "";
                }
            } else if (type == Cell.CELL_TYPE_FORMULA) {
                try {
                    data = numberFormat.format(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                    data = numberFormat.format(cell.getRichStringCellValue());
                }
            }
            //替换掉字符串中的","与"，"
            data = data.replace(",", "").replace("，", "");
        }
        return data;

    }

}
